package cn.demoncat.util.db;

import cn.demoncat.util.db.config.DbProperties;
import cn.demoncat.util.exception.DbRuntimeException;
import cn.demoncat.util.lang.JDBCUtil;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;

/**
 * 数据库工具
 *
 * 1、调用 buildRunner 构建实例
 * 2、调用实例方法，执行SQL，通过 DbResult 获取结果
 *
 * 1、SQL占位符 ?
 * 2、默认每条SQL是一个事务。要自定义事务时，自己获取Connection并传参。
 * 3、主动关闭连接：DbUtils.close
 *
 * @author 延晓磊
 * @since 2021年09月23日
 */
public class DbUtil extends QueryRunner{

    // 连接超时，对应Mysql配置 wait_timeout = 28800
    private static final int DEFAULT_MAX_LIFETIME = 28830000;

    /**
     * 构建
     *
     * @param ds
     */
    public DbUtil(DataSource ds) {
        super(ds);
    }

    /**
     * 创建连接池
     *
     * @param url
     * @param username
     * @param password
     * @param pollSize  最大连接数：默认10，建议 = CPU-CORE * 2 + 1
     * @param lifeTime  连接超时：毫秒，默认1800000；建议比数据库的wait_timeout多30秒（通常Mysql配置 wait_timeout = 28800，即28830000，值为-1时设置）
     * @return  HikariDataSource
     *
     * @author 延晓磊
     * @since 2021年09月23日
     */
    public static HikariDataSource buildDataSource(String url, String username, String password, int pollSize, int lifeTime){
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url);
        config.setDriverClassName(JDBCUtil.getDriver(url));
        config.setUsername(username);
        config.setPassword(password);
        config.setMaximumPoolSize(pollSize);
        config.setMaxLifetime(lifeTime <=0 ? DEFAULT_MAX_LIFETIME : lifeTime);
        return new HikariDataSource(config);
    }

    /**
     * 创建连接池
     *
     * @param  properties
     * @return  HikariDataSource
     *
     * @author 延晓磊
     * @since 2021年09月23日
     */
    public static HikariDataSource buildDataSource(DbProperties properties){
       return buildDataSource(properties.getUrl(), properties.getUsername(), properties.getPassword(), properties.getPollSize(), properties.getLifeTime());
    }

    /**
     * 创建DbUtil实例
     *
     * 1、批处理    .batch(sql, params)
     * 2、查询     .query(sql, ResultSetHandler, params)
     * 3、更新/删除 .update(sql, params)
     * 4、插入     .insert(sql, ResultSetHandler, params)
     *
     * 1、SQL占位符 ?
     * 2、默认每条SQL是一个事务。要自定义事务时，自己获取Connection并传参。
     * 3、主动关闭连接：DbUtils.close
     *
     * @param dataSource
     *
     * @return DbUtil
     *
     * @author 延晓磊
     * @since 2021年09月23日
     */
    public static DbUtil buildRunner(DataSource dataSource){
        return new DbUtil(dataSource);
    }

    /**
     * 创建DbUtil实例
     *
     * 1、批处理    .batch(sql, params)
     * 2、查询     .query(sql, ResultSetHandler, params)
     * 3、更新/删除 .update(sql, params)
     * 4、插入     .insert(sql, ResultSetHandler, params)
     *
     * 1、SQL占位符 ?
     * 2、默认每条SQL是一个事务。要自定义事务时，自己获取Connection并传参。
     * 3、主动关闭连接：DbUtils.close
     *
     * @param  properties   数据库配置
     *
     * @return DbUtil
     *
     * @author 延晓磊
     * @since 2021年09月23日
     */
    public static DbUtil buildRunner(DbProperties properties){
        return buildRunner(buildDataSource(properties));
    }

    /**
     * SELECT
     *
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param rsh The handler used to create the result object from the <code>ResultSet</code>.
     * @param params Initialize the PreparedStatement's IN parameters with this array.
     *
     * @return An object generated by the handler.
     */
    @Override
    public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)  {
        try {
            return super.query(sql, rsh, params);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }

    /**
     * Execute a batch of SQL INSERT, UPDATE, or DELETE queries
     *
     * @param sql The SQL to execute.
     * @param params An array of query replacement parameters.  Each row in this array is one set of batch replacement values.
     *
     * @return The number of rows updated per statement.
     */
    @Override
    public int[] batch(String sql, Object[][] params) {
        try {
            return super.batch(sql, params);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }

    /**
     * Execute an SQL statement, including a stored procedure call, which does not return any result sets.
     *
     * @param sql The SQL statement to execute.
     * @param params Initializes the CallableStatement's parameters (i.e. '?').
     *
     * @return The number of rows updated.
     */
    @Override
    public int execute(String sql, Object... params) {
        try {
            return super.execute(sql, params);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }

    /**
     * Execute an SQL statement, including a stored procedure call, which returns one or more result sets.
     *
     * @param <T> The type of object that the handler returns
     * @param sql The SQL to execute.
     * @param rsh The result set handler
     * @param params The query replacement parameters.
     *
     * @return A list of objects generated by the handler
     */
    @Override
    public <T> List<T> execute(String sql, ResultSetHandler<T> rsh, Object... params) {
        try {
            return super.execute(sql, rsh, params);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }

    /**
     * SELECT
     *
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param rsh The handler used to create the result object from the <code>ResultSet</code>.
     *
     * @return An object generated by the handler.
     */
    @Override
    public <T> T query(String sql, ResultSetHandler<T> rsh) {
        try {
            return super.query(sql, rsh);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }

    /**
     * INSERT
     *
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param rsh The handler used to create the result object from the <code>ResultSet</code> of auto-generated keys.
     *
     * @return An object generated by the handler.
     */
    @Override
    public <T> T insert(String sql, ResultSetHandler<T> rsh){
        try {
            return super.insert(sql, rsh);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }

    /**
     * INSERT
     *
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param rsh The handler used to create the result object from the <code>ResultSet</code> of auto-generated keys.
     * @param params Initializes the PreparedStatement's IN (i.e. '?')
     *
     * @return An object generated by the handler.
     */
    @Override
    public <T> T insert(String sql, ResultSetHandler<T> rsh, Object... params){
        try {
            return super.insert(sql, rsh, params);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }

    /**
     * Batch of INSERT
     *
     * @param <T> The type of object that the handler returns
     * @param sql The SQL statement to execute.
     * @param rsh The handler used to create the result object from the <code>ResultSet</code> of auto-generated keys.
     * @param params Initializes the PreparedStatement's IN (i.e. '?')
     *
     * @return The result generated by the handler.
     */
    @Override
    public <T> T insertBatch(String sql, ResultSetHandler<T> rsh, Object[][] params){
        try {
            return super.insertBatch(sql, rsh, params);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }

    /**
     * INSERT, UPDATE, or DELETE
     *
     * @param sql The SQL statement to execute.
     *
     * @return The number of rows updated.
     */
    @Override
    public int update(String sql) {
        try {
            return super.update(sql);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }

    /**
     * INSERT, UPDATE, or DELETE
     *
     * @param sql The SQL statement to execute.
     * @param params Initializes the PreparedStatement's IN (i.e. '?') parameters.
     *
     * @return The number of rows updated.
     */
    @Override
    public int update(String sql, Object... params) {
        try {
            return super.update(sql, params);
        } catch (SQLException e) {
            throw new DbRuntimeException(e);
        }
    }


}
